<?php
namespace app\cms\controller;
use app\common\base\_Db;
use app\common\base\Systrade;
use app\common\entity\RequestEntity;
use think\Controller;
use think\Db;
use think\Loader;


/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2018/10/9
 * Time: 17:02
 */
class Excel{
    //导出
    //##########################################################################
    /*
     * 天、周
     */
    public function DayExcel(){
        //获取请求参数
        $request_data = [
            $post_parm = new RequestEntity('Parm', '参数', true, 0),
        ];
        _Db::Validates($request_data);
        if ($post_parm->text == 1 ){
            $day_time = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
            $where = "pay_time between '" . date('Y-m-d',$day_time) . " 00:00:00'" . " and '" . date('Y-m-d',$day_time) . " 23:59:59'";
            $where_count = "oper_date between '" . date('Y-m-d',$day_time) . " 00:00:00'" . " and '" . date('Y-m-d',$day_time) . " 23:59:59'";
            $file_name = date('Ymd').'.xls';
        }elseif ($post_parm->text == 2 ){
            $timestamp = time();
            $start_time = strtotime(date('Y-m-d', strtotime("this week Monday", $timestamp)));
            $end_time = strtotime(date('Y-m-d', strtotime("this week Sunday", $timestamp))) + 24 * 3600 - 1;
            $where = "pay_time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
            $where_count = "oper_date between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
            $file_name = date('Ymd',$start_time).'-'.date('Ymd',$end_time).'.xls';
        }
        $list = Systrade::DayItem($where,$where_count)['tableData'];
        //dump($list);die();

        $path = dirname(__FILE__);
        //Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        $PHPExcel = new \PHPExcel();
        // print_r($PHPExcel);die;
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("订单信息");
        $PHPSheet->setCellValue("A1","序号");
        $PHPSheet->setCellValue("B1","订单流水号");
        $PHPSheet->setCellValue("C1","用户昵称");
        $PHPSheet->setCellValue("D1","付款时间");
        $PHPSheet->setCellValue("E1","付款金额");
        //$PHPSheet->setCellValue("F1","微信支付对账单");
        $PHPSheet->setCellValue("F1","电子发票信息");
        $PHPSheet->setCellValue("G1","发票状态");
        $PHPSheet->setCellValue("H1","发票类型");
        $PHPSheet->setCellValue("I1","订单状态");
        $PHPSheet->setCellValue("J1","乘车身份");
        $i = 2;
        foreach($list as $key => $value){
            $PHPSheet->setCellValue('A'.$i,''.$value['Order']);
            $PHPSheet->setCellValue('B'.$i,''.$value['OutTradeNo']);
            //$PHPSheet->setCellValue('C'.$i,''.$value['Openid']);
            $PHPSheet->setCellValue('C'.$i,''.$value['NickName']);
            $PHPSheet->setCellValue('D'.$i,''.$value['PayTime']);
            $PHPSheet->setCellValue('E'.$i,''.$value['PayMoney']);
            //$PHPSheet->setCellValue('F'.$i,''.$value['ReturnCache']);
            $PHPSheet->setCellValue('F'.$i,''.$value['InvoiceMessage']);
            $PHPSheet->setCellValue('G'.$i,''.$value['InvoiceState']);
            $PHPSheet->setCellValue('H'.$i,''.$value['InvoiceType']);
            $PHPSheet->setCellValue('I'.$i,''.$value['TradeState']);
            $PHPSheet->setCellValue('J'.$i,''.$value['Identity']);
            $i++;
        }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename='.$file_name);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");
    }
    /*
     * 月、年
     */
    public function MonthExcel(){
        //获取请求参数
        $request_data = [
            $post_parm = new RequestEntity('Parm', '参数', true, 0),
        ];
        _Db::Validates($request_data);
        if ($post_parm->text == 3 ){
            $start_time = mktime(0, 0, 0, date('m'), 1, date('Y'));
            $end_time = mktime(23, 59, 59, date('m'), date('t'), date('Y'));
            $where = "pay_time between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
            $where_count = "oper_date between '" . date('Y-m-d',$start_time) . " 00:00:00'" . " and '" . date('Y-m-d',$end_time) . " 23:59:59'";
            $list = Systrade::WeekItem($where,$where_count)['tableData'];
            $file_name = date('Ymd',$start_time).'-'.date('Ymd',$end_time).'.xls';
        }elseif ($post_parm->text == 4 ){
            $start_time = date('Y-m',mktime(0, 0, 0, 1, 1, date('Y')));
            $end_time = date('Y-m',mktime(23, 59, 59, 12, 31, date('Y')));
            $where = "DATE_FORMAT(pay_time, '%Y-%m') between '" . $start_time . "' and '" . $end_time . "'";
            $where_count = "DATE_FORMAT(oper_date, '%Y-%m') between '" . $start_time . "' and '" . $end_time . "'";
            $list = Systrade::YearItem($where,$where_count)['tableData'];
            $file_name = date('Ym',mktime(0, 0, 0, 1, 1, date('Y'))).'-'.date('Ym',mktime(23, 59, 59, 12, 31, date('Y'))).'.xls';
        }

        $path = dirname(__FILE__);
        //Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        $PHPExcel = new \PHPExcel();
        // print_r($PHPExcel);die;
        $PHPSheet = $PHPExcel->getActiveSheet();
        $PHPSheet->setTitle("订单信息");
        $PHPSheet->setCellValue("A1","序号");
        $PHPSheet->setCellValue("B1","时间");
        $PHPSheet->setCellValue("C1","乘车人数");
        $PHPSheet->setCellValue("D1","实付金额");
        $i = 2;
        foreach($list as $key => $value){
            $PHPSheet->setCellValue('A'.$i,''.$value['Order']);
            $PHPSheet->setCellValue('B'.$i,''.$value['PayTime']);
            $PHPSheet->setCellValue('C'.$i,''.$value['Count']);
            $PHPSheet->setCellValue('D'.$i,''.$value['PayMoney']);
            $i++;
        }
        $PHPWriter = \PHPExcel_IOFactory::createWriter($PHPExcel,"Excel2007");
        header('Content-Disposition: attachment;filename='.$file_name);
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        $PHPWriter->save("php://output");
    }
    //导入
    //#################################
    public function insert_excel(){
        Loader::import('PHPExcel.Classes.PHPExcel');
        Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
        Loader::import('PHPExcel.Classes.PHPExcel.Reader.Excel5');
        $PHPExcel = new \PHPExcel();
        //获取表单上传文件
        $file = request()->file('excel');
        // echo function_exists($connect->validate);die;
        $info = $file->validate(['size'=>15678,'ext'=>'xlsx,xls,csv'])->move(ROOT_PATH . 'public' . DS . 'excel');
        if ($info) {
            $exclePath = $info->getSaveName();
            //获取文件名
            $file_name = ROOT_PATH . 'public' . DS . 'excel' . DS . $exclePath;
            //上传文件的地址
            $objReader =\PHPExcel_IOFactory::createReader('Excel2007');
            $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');
            //加载文件内容,编码utf-8
            $excel_array=$obj_PHPExcel->getsheet(0)->toArray();
            // print_r($excel_array);die;
            //转换为数组格式
            array_shift($excel_array);
            //删除第一个数组(标题);
            $city = [];
            $error_infos = [];
            $login_info = [];
            $i=0;
            // $j=2;
            foreach($excel_array as $k=>$v) {
//                $sn = Db::name('agent')->where('agent_sn',$v[0])->find();
//                if ($sn) {
//                    $j = $k+2;
//                    $error_infos[$k] = "第{$j}条代理商编号已存在";
//                    $i++;
//                    continue;
//                }
                $city[$k]['name'] 		= $v[0];
                /*$city[$k]['agent_name'] 	= $v[1];
                $city[$k]['agent_phone'] 	= $v[2];
                $city[$k]['agent_fuzearea'] = $v[3];
                $city[$k]['root_id'] 		= $v[4];
                $city[$k]['agent_member'] 	= $v[5];
                $city[$k]['agent_event'] 	= $v[6];
                $city[$k]['agent_areainfo'] = $v[7];*/
                $i++;
            }
            // print_r($city);die;
            $success = Db::name('test')->insertAll($city);
            $error=$i-$success;
            $error_infos['msg'] = "总{$i}条，成功{$success}条，失败{$error}条。";
            /*if ($i>0 && $res2>0) {*/
            if ($i>0) {
                return json_encode(205,"xc",$error_infos);
            }else{
                return json_encode(300,'导入失败');
            }
            //批量插入数据
        } else {
            echo $file->getError();
        }
    }

}